package com.ybu.oodd.utils.excel;

import com.ybu.oodd.pojo.Job;
import com.ybu.oodd.service.JobService;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

@Component
public class ExcelUtil {

    private static final DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");

    @Autowired
    private JobService jobService;

    public List<Job> parseJobsFromExcel(MultipartFile file) throws IOException {
        List<Job> jobs = new ArrayList<>();
        try (InputStream is = file.getInputStream();
             Workbook workbook = WorkbookFactory.create(is)) {

            Sheet sheet = workbook.getSheetAt(0); // 读取第一个 sheet
            Row headerRow = sheet.getRow(0);

            // 获取列索引（根据表头）
            int companyNameCol = getColumnIndex(headerRow, "公司名称");
            int companyTypeCol = getColumnIndex(headerRow, "公司类型ID(1央国企，2外企，3民企，4事业单位，5银行)");
            int companyIndustryCol = getColumnIndex(headerRow, "公司行业");
            int jobTypeCol = getColumnIndex(headerRow, "招聘类型ID(6春招，7秋招，8补录，9秋招提前批，10日常实习，11暑期实习)");
            int workLocationCol = getColumnIndex(headerRow, "工作地点");
            List<Map<Integer, String>> list = jobService.getCandidateType();
            // 动态生成字符串
            String result = "招聘对象ID(" +
                    list.stream()
                            .map(map -> String.valueOf(map.get("id")) + ":" + String.valueOf(map.get("value")))
                            .collect(Collectors.joining("，")) +
                    ")";
            int candidateCol = getColumnIndex(headerRow, result);
            int eduBackgroundCol = getColumnIndex(headerRow, "最低学历ID(12专科，13本科，14硕士，15博士)");
            int jobNameCol = getColumnIndex(headerRow, "岗位名称");
            int jobNumCol = getColumnIndex(headerRow, "岗位数量");
            int updateTimeCol = getColumnIndex(headerRow, "更新时间");
            int datelineCol = getColumnIndex(headerRow, "截止时间");
            int jobLinkCol = getColumnIndex(headerRow, "岗位链接");
            int noticeLinkCol = getColumnIndex(headerRow, "公告链接");

            // 遍历数据行（从第1行开始）
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null) continue;

                Job job = new Job();

                job.setCompanyName(getCellValue(row.getCell(companyNameCol)));
                job.setCompanyTypeId(parseInt(getCellValue(row.getCell(companyTypeCol))));
                job.setCompanyIndustry(getCellValue(row.getCell(companyIndustryCol)));
                job.setJobTypeId(parseInt(getCellValue(row.getCell(jobTypeCol))));
                job.setWorkLocation(getCellValue(row.getCell(workLocationCol)));
                job.setCandidateId(parseInt(getCellValue(row.getCell(candidateCol))));
                job.setEducationalBackgroundId(parseInt(getCellValue(row.getCell(eduBackgroundCol))));
                job.setJobName(getCellValue(row.getCell(jobNameCol)));
                job.setJobNum(parseInt(getCellValue(row.getCell(jobNumCol))));

                // 日期处理
                String updateTimeStr = getCellValue(row.getCell(updateTimeCol));
                if (updateTimeStr != null && !updateTimeStr.isEmpty()) {
                    job.setUpdateTime(LocalDate.parse(updateTimeStr, formatter));
                }

                String datelineStr = getCellValue(row.getCell(datelineCol));
                if (datelineStr != null && !datelineStr.isEmpty()) {
                    job.setDateline(LocalDate.parse(datelineStr, formatter));
                }

                job.setJobLink(getCellValue(row.getCell(jobLinkCol)));
                job.setNoticeLink(getCellValue(row.getCell(noticeLinkCol)));

                jobs.add(job);
            }
        }
        return jobs;
    }

    // 获取表头列索引
    private static int getColumnIndex(Row headerRow, String headerName) {
        for (int i = 0; i < headerRow.getLastCellNum(); i++) {
            if (headerName.equals(getCellValue(headerRow.getCell(i)))) {
                return i;
            }
        }
        throw new IllegalArgumentException("未找到表头列: " + headerName);
    }

    // 获取单元格字符串值
    private static String getCellValue(Cell cell) {
        if (cell == null) return null;
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue().trim();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getLocalDateTimeCellValue().toLocalDate().toString();
                } else {
                    return String.valueOf((int) cell.getNumericCellValue()); // 避免 1.0
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            default:
                return null;
        }
    }

    // 安全解析整数
    private static Integer parseInt(String value) {
        if (value == null || value.isEmpty()) return null;
        try {
            return Integer.parseInt(value);
        } catch (NumberFormatException e) {
            return null;
        }
    }

    /**
     * 生成模板 Excel 文件
     *
     * @return ByteArrayInputStream 包含模板 Excel 文件的输入流
     * @throws IOException 如果发生 I/O 错误
     */
    public ByteArrayInputStream generateTemplateExcel() throws IOException {
        // 创建新的工作簿和 sheet
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("Job Template");

        // 动态生成的 "招聘对象ID" 字符串
        List<Map<Integer, String>> list = jobService.getCandidateType();
        String candidateTypeIdString = "招聘对象ID(" +
                list.stream()
                        .map(map -> String.valueOf(map.get("id")) + ":" + String.valueOf(map.get("value")))
                        .collect(Collectors.joining("，")) +
                ")";

        // 定义表头
        String[] headers = {
                "公司名称",
                "公司类型ID(1央国企，2外企，3民企，4事业单位，5银行)",
                "公司行业",
                "招聘类型ID(6春招，7秋招，8补录，9秋招提前批，10日常实习，11暑期实习)",
                "工作地点",
                candidateTypeIdString,
                "最低学历ID(12专科，13本科，14硕士，15博士)",
                "岗位名称",
                "岗位数量",
                "更新时间",
                "截止时间",
                "岗位链接",
                "公告链接"
        };

        // 创建表头行
        Row headerRow = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(headers[i]);
        }

        // 将工作簿写入 ByteArrayOutputStream
        ByteArrayOutputStream out = new ByteArrayOutputStream();
        workbook.write(out);
        workbook.close();

        return new ByteArrayInputStream(out.toByteArray());
    }
}